package net.wantongtech.xa.uploadbasicinfo.dao;

import com.google.common.collect.Lists;
import com.wtkj.dao.impl.BaseDataTemplate;
import com.wtkj.dao.impl.GenericResultSetExtractor;
import com.wtkj.vo.dzzf.base.kfwd.CardInfo;
import com.wtkj.vo.dzzf.base.xxjh.MessageSend;
import com.wtkj.vo.dzzf.share.staticvalue.CardStaticValue;
import net.wantongtech.xa.uploadbasicinfo.util.ClearingCommonUtils;
import net.wantongtech.xa.uploadbasicinfo.vo.msg.DetailVo;
import net.wantongtech.xa.uploadbasicinfo.vo.msg.TransactionVo;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.util.List;
import java.util.stream.Collectors;

/**
 * 基础DAO实现
 *
 * @author MaShibo
 * 日期 :  2020/1/2
 */
@Repository
public class CommonBaseDao extends BaseDataTemplate {


    public List<MessageSend> queryBOCMessageSend() {
        final String sql =
                "SELECT *\n" +
                        "  FROM etc_messagesend a\n" +
                        " WHERE a.channeltype = ?\n" +
                        "   AND a.signsource = ?\n" +
                        "   AND send <> ?";
        return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(MessageSend.class),
                "BOC", "1", 5);
    }


    public List<TransactionVo> queryIntegratedTransById(List<String> transactionIdList, String channelType) {
        final String sql =
                "SELECT e.transactionid,\n" +
                        "      to_char(tr.opton, 'yyyy-MM-dd hh24:mi:ss') AS extime,\n" +
                        "      tr.paymny * 100 AS fee,\n" +
                        "      to_char(e.endatetime, 'yyyy-MM-dd hh24:mi:ss') AS entime,\n" +
                        "      tr.cardnetid || tr.ecardno,\n" +
                        "      tr.obucontractno AS obuno,\n" +
                        "      e.entollstationname,\n" +
                        "      e.extollstationname,\n" +
                        "      tr.originalserviceid AS tollprovinceid,\n" +
                        "      tr.servicetypeformsg AS servicetype,\n" +
                        "      tr.description\n" +
                        " FROM tr_transout tr\n" +
                        " JOIN tr_transout_extend e\n" +
                        "   ON tr.consumeid = e.consumeid\n" +
                        "WHERE tr.originalissuerid = ?\n" +
                        "  AND e.channeltype = ?\n" +
                        "  AND e.transactionid IN (%s)";
        return jdbcTemplate.query(String.format(sql, String.join(",", transactionIdList.stream().map(v -> String.format("'%s'", v)).collect(Collectors.toList()))), (rs, i) -> {
            TransactionVo transactionVo = new TransactionVo();
            transactionVo.setIntegratedId(rs.getString(1));
            transactionVo.setExTime(rs.getString(2));
            transactionVo.setFee(rs.getLong(3));
            transactionVo.setEnTime(rs.getString(4));
            transactionVo.setCardId(rs.getString(5));
            transactionVo.setObuNo(rs.getString(6));
            transactionVo.setEnTollStationName(rs.getString(7));
            transactionVo.setExTollStationName(rs.getString(8));
            transactionVo.setTollProvinceId(ClearingCommonUtils.convert2NewTransferNo(rs.getString(9)));
            transactionVo.setServiceType(rs.getInt(10));
            transactionVo.setDescription(rs.getString(11));
            transactionVo.setObuSign(2);
            transactionVo.setInProvince(StringUtils.endsWith("610101", transactionVo.getTollProvinceId()) ? 1 : 0);
            transactionVo.setIsIntegrated(0);
            DetailVo detailVo = new DetailVo();
            detailVo.setTransactionId(transactionVo.getIntegratedId());
            detailVo.setFee(transactionVo.getFee());
            transactionVo.setDetails(Lists.newArrayList(detailVo));
            return transactionVo;
        }, "9999999961010001", channelType);
    }


    public Long querySeqNetVal(String seqName) {
        final String sql = String.format("SELECT %s.nextval FROM dual", seqName);
        return jdbcTemplate.queryForObject(sql, Long.class);
    }


    public CardInfo queryCardInfoByECardNo(String eCardNo) {
        final String sql = "SELECT *\n" +
                "  FROM (SELECT a.*\n" +
                "          FROM ac_cardinfo a\n" +
                "          LEFT JOIN ac_account t\n" +
                "            ON a.accountid = t.accountid\n" +
                "          LEFT JOIN ac_fundaccount f\n" +
                "            ON t.fundaccountid = f.fundaccountid\n" +
                "         WHERE a.ecardno = ?\n" +
                "           AND a.ecardstatus <> ?\n" +
                "        UNION ALL\n" +
                "        SELECT a.*\n" +
                "          FROM ac_cardinfo a\n" +
                "          JOIN ac_cardveh v\n" +
                "            ON a.cardinfoid = v.cardinfoid\n" +
                "          LEFT JOIN ac_account t\n" +
                "            ON a.accountid = t.accountid\n" +
                "          LEFT JOIN ac_fundaccount f\n" +
                "            ON t.fundaccountid = f.fundaccountid\n" +
                "         WHERE v.ecardno = ?)\n" +
                " ORDER BY opton DESC";
        List<CardInfo> cardInfoList = jdbcTemplate.query(sql, GenericResultSetExtractor.getInstance(CardInfo.class), eCardNo,
                CardStaticValue.EcardStatus.EcardState_SQWQR, eCardNo);
        return CollectionUtils.isEmpty(cardInfoList) ? null : cardInfoList.get(0);
    }

}
